

ALTER    proc load_max_lineItemNo

@salesOrder_id int, @lineItem_No int output

as

/*
 * Select the maximum lineItemNo From the Customer_Cancel_Order_LineItem
 */
declare @cancel_order_lineItemNo int

select @cancel_order_lineItemNo = (Select max(CCOL_Line_Item_No) FROM 
	Customer_Cancel_Order_LineItem Where SalesOrder_ID = @salesOrder_id)


if(@cancel_order_lineItemNo is null)
	set @cancel_order_lineItemNo = 0
/*
 * Select the maximum lineItemNo From the Customer_Open_Order_LineItem
 */
declare @open_order_lineItemNo int

select @open_order_lineItemNo = (Select max(COOL_Line_Item_No) FROM 
	Customer_Open_Order_LineItem Where SalesOrder_Id = @salesOrder_id)


/*
 * Check the selected values and set to output parameter
 */
if(@open_order_lineItemNo > @cancel_order_lineItemNo)
	set @lineItem_No = @open_order_lineItemNo
else
	set @lineItem_No = @cancel_order_lineItemNo


/*
 * Return the outparameter
 */ 
select @lineItem_No

return




